package db.util;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import java.util.TreeSet;

import db.util.Table.Index;

import task.DbTask;

public class DbStructure extends DbTask {
	String schema;
	TreeMap<String, Table> tables;
	public DbStructure() {
		
	}
	public DbStructure(String schema, TreeMap<String, Table> tables, DbType dbType) {
		this.schema = schema;
		this.tables = tables;
		this.dbType = dbType;
	}
	
	
	@Override
	protected void doRun(Map<String, String> propMap) throws SQLException {
		String schema = propMap.get("schema");
		String outFile = propMap.get("outFile");
		DbStructure dbs = create(conn(), dbType, schema);
		System.out.printf("%s%n", dbs);
		if (outFile != null && outFile.trim().length()>0) {
			FileWriter os;
			try {
				File file = new File(outFile); 
				os = new FileWriter(file);
				XmlIO.toXML(dbs, os);
				os.close();
				System.out.printf("DbStructure written to file: '%s'%n", file.getCanonicalPath());
			} catch (IOException e) {
				throw new RuntimeException(e);
			}
		}
	}
	public static DbStructure create(Connection conn, DbType dbType, String schema) throws SQLException {
		ResultSet rs;
		DbStructure result;
		switch (dbType) {
		case POSTGRES:
			Set<String> tableNames = getTableNames(conn, schema);
//			HashSet<String> primKeyNames = getPrimaryKeys(conn, schema, tableNames);
//			getIndexInfo(conn, schema, tableNames, primKeyNames);
			rs = conn.getMetaData().getColumns(null, schema, null, null);
			result = create(rs, schema, tableNames, conn, dbType);
			rs.close();
			return result;
		case ORACLE:
			String sql = String.format("select TABLE_NAME, COLUMN_NAME, " +
					"DATA_TYPE as TYPE_NAME, DATA_LENGTH as " +
					"COLUMN_SIZE, NULLABLE, DATA_PRECISION as DECIMAL_DIGITS, " +
					"DATA_SCALE from ALL_TAB_columns where owner='%s' " +
					"order by TABLE_NAME, COLUMN_ID", schema.toUpperCase());
			Statement stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			result = create(rs, schema, null, conn, dbType);
			rs.close();
			stmt.close();
			return result;
		default:
			System.out.printf("Unknown dbType: %s%n", dbType);
			break;
		}
		return null;
	}
	
	private static DbStructure create(ResultSet rs, String schema, Set<String> tableNames, Connection conn, DbType dbType) throws SQLException {
		TreeMap<String, Table> tables = new TreeMap<String, Table>();
		ArrayList<Column> columns = new ArrayList<Column>();
		String prevTable = null;
		while (rs.next()) {
			String currTable = rs.getString("TABLE_NAME").toLowerCase();
			if (!currTable.equals(prevTable)) {
				if (prevTable != null) {
					Column[] cols = columns.toArray(new Column[0]);
					if (tableNames == null || tableNames.contains(prevTable)) {
						tables.put(prevTable, new Table(prevTable, cols));
					}
					columns.clear();
				}
				prevTable = currTable;
			}
			Integer decDigits = rs.getInt("DECIMAL_DIGITS");
			columns.add(new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE"), toBoolean(rs.getString("NULLABLE")), decDigits));
		}

		rs.close();
		if (prevTable != null) {
			Column[] cols = columns.toArray(new Column[0]);
			tables.put(prevTable, new Table(prevTable, cols));
			columns.clear();
		}
		for (Table table : tables.values()) {
			addPrimaryKeyAndIndexes(schema, table, conn, dbType);
		}
		return new DbStructure(schema, tables, dbType);
	}

	private static boolean toBoolean(String s) {
		if (s.toUpperCase().charAt(0) == 'Y') return true;
		if (s.toUpperCase().charAt(0) == 'N') return false;
		if (s.toUpperCase().charAt(0) == '1') return true;
		if (s.toUpperCase().charAt(0) == '0') return false;
		System.out.printf("***** ===== UNKNOWN: %s%n",s);
		return false;
	}
	private static void listTypes(Connection conn) throws SQLException {
		ResultSet rs = conn.getMetaData().getTypeInfo();
		while (rs.next()) {
			System.out.printf("name:%s type:%s prec:%s prefix:%s, suffix:%s params:%s nullable:%s case:%s " +
					"searchable:%s unsigned:%s fixedPrecScale:%s autoInc:%s locTypeName:%s minScale:%s " +
					"maxScale:%s sqlDataType:%s sqlDateTimeSub:%s numPrecRadix:%s%n"
					, rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5)
					, rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), 
					rs.getString(11), rs.getString(12), rs.getString(13), rs.getString(14), rs.getString(15)
					, rs.getString(16), rs.getString(17), rs.getString(18));
		}
		rs.close();
	}
	private static void addPrimaryKeyAndIndexes(String schema, Table table, Connection conn, DbType dbType) throws SQLException {
		if (dbType == DbType.ORACLE) {
			oracleAddPrimaryKeyAndIndexes(schema, table, conn);
			return;
		}
		ResultSet rs = conn.getMetaData().getPrimaryKeys(null, schema, table.getName());
		String pkName = null;
		ArrayList<Integer> positions = new ArrayList<Integer>();
		while (rs.next()) {
			String colName = rs.getString("COLUMN_NAME");
			int ndx = table.gteColumnIndex(colName);
			positions.add(ndx);
			pkName = rs.getString("PK_NAME");
		}
		if (pkName != null) {
			int[] tab = new int[positions.size()];
			for (int i=0 ; i<tab.length ; i++) {
				tab[i] = positions.get(i);
			}
			Table.Index primKey = table.createIndex(pkName, false, tab);
			table.setPrimaryKey(primKey);
		}
		rs.close();
		positions.clear();
		String prevNdxName = null;
		boolean nonUnique = false;
		rs = conn.getMetaData().getIndexInfo(null, schema, table.getName(), false, false);
		ArrayList<Table.Index> indexes = new ArrayList<Table.Index>();
		while (rs.next()) {
			String ndxName = rs.getString("INDEX_NAME");
			if (ndxName.equals(pkName)) continue;
			if (!ndxName.equals(prevNdxName) && prevNdxName != null) {
				indexes.add(createIndex(table, prevNdxName, nonUnique, positions));
				positions.clear();
			}
			prevNdxName = ndxName;
			nonUnique = rs.getBoolean("NON_UNIQUE");
//			int ordPos = rs.getInt("ORDINAL_POSITION");
			String colName = rs.getString("COLUMN_NAME");
			int ndx = table.gteColumnIndex(colName);
			positions.add(ndx);
		}
		if (prevNdxName != null) {
			indexes.add(createIndex(table, prevNdxName, nonUnique, positions));
		}
		rs.close();
		table.setIndexes(indexes.toArray(new Index[0]));
	}
	private static void oracleAddPrimaryKeyAndIndexes(String schema, Table table, Connection conn) throws SQLException {
		String sql = "SELECT cols.position, index_name, cols.column_name " +
				"FROM all_constraints cons, all_cons_columns cols " +
				"WHERE cols.table_name = ? " +
				"AND cons.constraint_type = 'P' " +
				"AND cons.constraint_name = cols.constraint_name " +
				"AND cons.owner = cols.owner " +
				"AND cons.owner = ? " +
				"ORDER BY cols.position";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, table.getName().toUpperCase());
		stmt.setString(2, schema.toUpperCase());
		ResultSet rs = stmt.executeQuery();
		String primName = null;
		ArrayList<Integer> positions = new ArrayList<Integer>();
		while (rs.next()) {
			String colName = rs.getString(3).toLowerCase();
//			int pos = rs.getInt(1);
			int pos = table.gteColumnIndex(colName);
			positions.add(pos);
			primName = rs.getString(2).toLowerCase();
		}
		rs.close();
		stmt.close();
		if (primName != null) {
			Index primKey = createIndex(table, primName, false, positions);
			table.setPrimaryKey(primKey);
		}
		sql = "select c.COLUMN_NAME, c.COLUMN_POSITION, c.INDEX_NAME, i.UNIQUENESS  " +
				"from ALL_IND_COLUMNS c, ALL_INDEXES i " +
				"where c.TABLE_NAME=? " +
				"and c.INDEX_OWNER=? " +
				"and c.INDEX_OWNER=i.OWNER " +
				"and c.INDEX_NAME=i.INDEX_NAME " +
				"order by c.INDEX_NAME, c.COLUMN_POSITION ";
		stmt = conn.prepareStatement(sql);
		stmt.setString(1, table.getName().toUpperCase());
		stmt.setString(2, schema.toUpperCase());
		rs = stmt.executeQuery();
		String prevNdxName = null;
		boolean nonUnique = false;
		positions.clear();
		ArrayList<Table.Index> indexes = new ArrayList<Table.Index>();
		while (rs.next()) {
			String ndxName = rs.getString(3).toLowerCase();
			if (ndxName.equals(primName)) continue;
			if (!ndxName.equals(prevNdxName)) {
				if (prevNdxName != null) {
					Index ndx = createIndex(table, prevNdxName, nonUnique, positions);
					indexes.add(ndx);
				}
				prevNdxName = ndxName;
			}
			nonUnique = "NONUNIQUE".equals(rs.getString(4));
			String colName = rs.getString(1).toLowerCase();
			int p = table.gteColumnIndex(colName);
			positions.add(p);
		}
		rs.close();
		stmt.close();
		if (prevNdxName != null) {
			Index ndx = createIndex(table, prevNdxName, nonUnique, positions);
			indexes.add(ndx);
		}
		Index[] indexTab = indexes.toArray(new Index[0]);
		table.setIndexes(indexTab);
	}
	private static Index createIndex(Table table, String ndxName,
			boolean nonUnique, ArrayList<Integer> positions) {
		int[] tab = new int[positions.size()];
		for (int i=0 ; i<tab.length ; i++) {
			tab[i] = positions.get(i);
		}
		Table.Index index = table.createIndex(ndxName, nonUnique, tab);
		return index;
	}
//	private static void show(ResultSet rs) throws SQLException {
//		int n =	rs.getMetaData().getColumnCount();
//		for (int i=0 ; i<n ; i++) {
//			System.out.printf(" %2s %s%n",i+1, rs.getMetaData().getColumnName(i+1));
//		}
//	}
	private static Set<String> getTableNames(Connection conn, String schema) throws SQLException {
		String[] types = { "TABLE" };
		ResultSet rs = conn.getMetaData().getTables(null, schema, null, types);
		TreeSet<String> tableNames = new TreeSet<String>();
		while (rs.next()) {
			tableNames.add(rs.getString(3));
		}
		rs.close();
		return tableNames;
	}
	@Override
	public String[] neededProperties() {
		String[] props = { "schema", "outFile" };
		return props;
	}
	
	@Override
	public String getDescription() {
		return "Constructs a DbStructure, prints it to stdout,\n " +
				"and if outFile is set, writes it to file";
	}
	
	@Override
	public String toString() {
		StringBuilder sb = new StringBuilder();
		sb.append("schema: " + schema + "(\n");
		for (Table t : tables.values()) {
			sb.append(t);
		}
		sb.append(")\n");
		return sb.toString();
	}
	public boolean equals(DbStructure b, StringTree os, ColumnComparator cmp) {
		TreeSet<String> missingInA = new TreeSet<String>();
		ArrayList<String> missingInB = new ArrayList<String>();
		missingInA.addAll(b.tables.keySet());
		boolean result = true;
		for (Table table : tables.values()) {
			Table bTable = b.tables.get(table.getName());
			if (bTable == null) {
				missingInB.add(table.getName());
			} else {
				missingInA.remove(table.getName());
				StringTree heading = os.branch();
				if (!table.equals(bTable, os, cmp)) {
					heading.printf("Table '%s'%n", table.getName());
					result = false;
				}
			}
		}
		if (missingInA.size() > 0) {
			result = false;
			os.printf("These tables are missing in A:%n");
			for (String name : missingInA) {
				os.printf("  %s%n", name);
			}
		}
		if (missingInB.size() > 0) {
			result = false;
			os.printf("These tables are missing in B:%n");
			for (String name : missingInB) {
				os.printf("  %s%n", name);
			}
		}
		return result;
	}
	public DbType getDbType() {
		return dbType;
	}
}
